﻿--
-- Version table
--

CREATE TABLE IF NOT EXISTS [Version] (
[VersionID] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Major] INTEGER NOT NULL,
[Minor] INTEGER NOT NULL,
[Build] INTEGER NOT NULL,
[Revision] INTEGER NOT NULL
);

--
-- Subscriptions table
--

DROP TABLE IF EXISTS [Subscription];

CREATE TABLE IF NOT EXISTS [Subscription] (
[SubscriptionID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Guid] NVARCHAR(255) UNIQUE NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[FeedUrl] NVARCHAR(255) NOT NULL,
[ImageUrl] NVARCHAR(255) NULL,
[Whuffie] INTEGER DEFAULT 0,
[LastUpdated] DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
[Deleted] INTEGER DEFAULT 0,
[Description] TEXT NULL
);


--
-- Episodes table
--

DROP TABLE IF EXISTS [Episode];

CREATE TABLE IF NOT EXISTS [Episode] (
[EpisodeID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Guid] NVARCHAR(255) UNIQUE NOT NULL,
[Name] NVARCHAR(100) NOT NULL,
[FeedUrl] NVARCHAR(255) NOT NULL,
[PublicationDate] DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
[Duration] NVARCHAR(10) NULL,
[FileSize] INTEGER DEFAULT 0,
[SubscriptionID] INTEGER NOT NULL,
[PodcastName] NVARCHAR(100) NULL,
[Age] INTEGER DEFAULT 0,
[Stage] INTEGER DEFAULT 0,
[Description] TEXT NULL
);

DROP TABLE IF EXISTS [MediaDevice];

CREATE TABLE IF NOT EXISTS [MediaDevice] (
[MediaDeviceID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
[Guid] NVARCHAR(255) UNIQUE NOT NULL,
[Enabled] INTEGER DEFAULT 0,
[VolumeName] TEXT NULL,
[BaseFolder] TEXT NULL,
[OrganizationOption] INTEGER DEFAULT 0,
[EjectOption] INTEGER DEFAULT 0,
[TransferOption]  INTEGER DEFAULT 0
);


--
-- Thumbnails table
-- 

--CREATE TABLE IF NOT EXISTS [Thumbnail] (
--[ThumbnailID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
--[Guid] NVARCHAR(255) UNIQUE NOT NULL,
--[Image] BLOB NULL
--);

--
-- Media table
-- 

--CREATE TABLE IF NOT EXISTS [Media] (
--[MediaID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
--[Guid] NVARCHAR(40) UNIQUE NOT NULL,
--[SourcePath] NVCHAR(255) NOT NULL,
--[TargetPath] NVCHAR(255) NOT NULL
--);


--
-- DownloadHistory table
-- 

--CREATE TABLE IF NOT EXISTS [History] (
--[HistoryID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
--[Guid] NVARCHAR(40) UNIQUE NOT NULL,
--[Name] NVARCHAR(100) NOT NULL,
--[FeedUrl] NVARCHAR(255) NOT NULL,
--[PublicationDate] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
--[SubscriptionID] INTEGER NOT NULL
--);

-- 
-- Log table
-- 

--CREATE TABLE IF NOT EXISTS [Log] (
--[LogID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,
--[TimeStamp] TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
--);
--
-- Initial data...
--

DROP TABLE IF EXISTS [Thumbnail];
DROP TABLE IF EXISTS [Log];
DROP TABLE IF EXISTS [History];
DROP TABLE IF EXISTS [Media];

